/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cc.altius.dao.impl;

import cc.altius.dao.HospitalDao;
import cc.altius.model.*;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.hibernate.*;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author sagar
 */
@Repository("HospitalDao")
public class HospitalDaoImpl implements HospitalDao {

    private static int CENTER_ID_YET_TO_CHOOSE = 5;
    private SessionFactory sessionFactory;
    private HibernateTemplate hibernateTemplate;
    @Autowired
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
    Session session;
    Query query;
    private static int TIE_UP_STAGE=3;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Autowired
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
        this.hibernateTemplate = new HibernateTemplate(sessionFactory);
    }

    @Override
    @Transactional
    public int addHospital(Hospital hospital) {
        System.out.println("in dao implof addHospital");
        try {
            return (Integer) this.hibernateTemplate.save(hospital);
        } catch (Exception e) {
            System.out.println("" + e.getMessage());
            return 0;
        }
    }

    @Override
    @Transactional
    public int updateHospital(Hospital hospital, int hospitalId) {
        session = this.sessionFactory.getCurrentSession();
        try {
            String hql = "Update "
                    + " Hospital as hsp "
                    + " set "
                    + " hsp.hospitalName=:hospitalName,"
                    + " hsp.level=:level,"
                    + " hsp.zoneId.zoneId=:zoneId,"
                    + " hsp.bedDesc=:bedDesc,"
                    + " hsp.tieUpStage=:tieUpStage,"
                    + " hsp.ipd=:ipd,"
                    + " hsp.opd=:opd,"
                    + " hsp.dayCare=:dayCare,"
                    + " hsp.diagnostics=:diagnostics,"
                    + " hsp.ambulanceService=:ambulanceService,"
                    + " hsp.contact1=:contact1,"
                    + " hsp.contact2=:contact2,"
                    + " hsp.contact3=:contact3,"
                    + " hsp.latitude=:latitude,"
                    + " hsp.longitude=:longitude,"
                    + " hsp.speciality=:speciality, "
                    + " hsp.isInsurance=:isInsurance, "
                    + " hsp.depositLess=:depositLess, "
                    + " hsp.hospitalAddress=:hospitalAddress "
                    + " WHERE hsp.hospitalId=:hospitalId";
            query = session.createQuery(hql);
            query.setParameter("hospitalName", hospital.getHospitalName());
            query.setParameter("level", hospital.getLevel());
            query.setParameter("zoneId", hospital.getZoneId().getZoneId());
            query.setParameter("bedDesc", hospital.getBedDesc());
            query.setParameter("tieUpStage", hospital.getTieUpStage());
            query.setParameter("ipd", hospital.getIpd());
            query.setParameter("opd", hospital.getOpd());
            query.setParameter("dayCare", hospital.getDayCare());
            query.setParameter("diagnostics", hospital.getDiagnostics());
            query.setParameter("ambulanceService", hospital.getAmbulanceService());
            query.setParameter("contact1", hospital.getContact1());
            if (hospital.getContact2() == null) {
                query.setParameter("contact2", null);
            } else {
                query.setParameter("contact2", hospital.getContact2());
            }
            if (hospital.getContact3() == null) {
                query.setParameter("contact3", null);
            } else {
                query.setParameter("contact3", hospital.getContact3());
            }
            query.setParameter("latitude", hospital.getLatitude());
            query.setParameter("longitude", hospital.getLongitude());
            query.setParameter("hospitalAddress", hospital.getHospitalAddress());
            //query.setParameter("latitude", hospital.getLatitude());
            //query.setParameter("longitude", hospital.getLongitude());
            if (hospital.getSpeciality() == null) {
                query.setParameter("speciality", null);
            } else {
                query.setParameter("speciality", hospital.getSpeciality());
            }
            query.setParameter("isInsurance", hospital.isIsInsurance());
            query.setParameter("depositLess", hospital.isDepositLess());
            query.setParameter("hospitalId", hospital.getHospitalId());
            int rowCount = query.executeUpdate();
            return rowCount;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    @Transactional
    public List<Hospital> getHospitalList() {
        List<Hospital> hospital = this.hibernateTemplate.find("from Hospital");
        System.out.println("hospitallist" + hospital);
        return hospital;
    }

    @Override
    @Transactional
    public Hospital getHospitalById(int hospitalId) {

        session = this.sessionFactory.getCurrentSession();
        query = session.createQuery("from Hospital where hospitalId=" + hospitalId);
        return (Hospital) query.uniqueResult();
    }

    @Override
    @Transactional
    public List<Zone> getZoneList() {
        List<Zone> zone = this.hibernateTemplate.find("from Zone");
        return zone;
    }

    @Override
    @Transactional
    public Zone getZoneById(int zoneId) {
        session = this.sessionFactory.getCurrentSession();
        query = session.createQuery("from Zone where zoneId=" + zoneId);
        return (Zone) query.uniqueResult();
    }

    @Override
    @Transactional
    public Hospital getHospitalByHospitalName(String hospitalName) {
        System.out.println("inside getHospitalByHospitalName" + hospitalName);
        //String sqlString="SELECT hospital.*,zone.ZONE_ID,master_zone.MASTER_ZONE_ID,city.CITY_ID,state.STATE_ID FROM hospital LEFT JOIN zone ON hospital.ZONE_ID = zone.ZONE_ID LEFT JOIN master_zone ON zone.MASTER_ZONE_ID=master_zone.MASTER_ZONE_ID LEFT JOIN city On city.CITY_ID=master_zone.CITY_ID LEFT JOIN state ON city.STATE_ID=state.STATE_ID WHERE hospital.HOSPITAL_NAME=?";
        //String sqlString="SELECT hospital.*,zone.* FROM hospital LEFT JOIN zone ON hospital.ZONE_ID = zone.ZONE_ID   LEFT JOIN master_zone ON zone.MASTER_ZONE_ID=master_zone.MASTER_ZONE_ID LEFT JOIN city On city.CITY_ID=master_zone.CITY_ID LEFT JOIN state ON city.STATE_ID=state.STATE_ID WHERE hospital.HOSPITAL_NAME=?";
//        String sqlString="SELECT hospital.*,zone.ZONE_ID,master_zone.`MASTER_ZONE_ID`,city.`CITY_ID`,state.`STATE_ID` FROM hospital LEFT JOIN zone ON hospital.ZONE_ID = zone.ZONE_ID LEFT JOIN master_zone ON zone.MASTER_ZONE_ID=master_zone.MASTER_ZONE_ID LEFT JOIN city ON city.CITY_ID=master_zone.CITY_ID LEFT JOIN state ON city.STATE_ID=state.STATE_ID WHERE hospital.HOSPITAL_NAME=?";
//        Object param[]= new Object[]{hospitalName};
//        try{
//            System.out.println("before query for object");
//        return this.jdbcTemplate.queryForObject(sqlString, param, new HospitalRowMapper());
//        }
//        catch(EmptyResultDataAccessException e){
//        return null;
//        }

        //session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
        //query = session.createQuery("from Hospital WHERE hospitalName=:hospitalName");
        session = this.sessionFactory.getCurrentSession();
        query = session.createQuery("from Hospital WHERE hospitalName" + hospitalName);
        //query.setParameter("hospitalName", hospitalName);
        return (Hospital) query.uniqueResult();
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getListOnZipCode(String zipCode) {
        List<Map<String, Object>> list = null;
        try {
            session = this.sessionFactory.getCurrentSession();
            String hql = "select "
                    + "new map(zip.zipCode as zipCode,"
                    + "masterZone.masterZoneCode  as masterZoneCode,"
                    + "city.cityName as cityName,"
                    + "state.stateName as stateName,"
                    + "zip.area as area)"
                    + " FROM ZipCodeArea as zip "
                    + " LEFT JOIN zip.masterZone as  masterZone "
                    + " LEFT JOIN masterZone.cityId as  city "
                    + " LEFT JOIN city.stateId as  state "
                    + " WHERE  zip.zipCode=:zipCode ";
//String hql="Select "
//        + "zipCode as zipCode,"
//        + "zipCodeId as zipCodeId,"
//        + "area as area,"
//        + "masterZone.masterZoneCode as masterZoneCode,"
//        + "from ZipCodeArea as zip "
//        + "L"

            query = session.createQuery(hql);
            System.out.println("zip " + zipCode);
            query.setParameter("zipCode", zipCode);
            list = query.list();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Hospital> getHospitalByZoneId(int zoneId) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    @Transactional
    public List<Map<String, Object>> getFilterHospital(int stateId, int cityId, int masterZoneId, int zoneId,int tieUpId) {

        String sql = "SELECT  "
                + "hospital.HOSPITAL_ID,hospital.HOSPITAL_NAME,"
                + "hospital.LEVEL,"
                + "hospital.TIE_UP_STAGE,"
                + "hospital.AMBULANCE_SERVICE,"
                + "hospital.CONTACT_1,"
                + "hospital.CONTACT_2,"
                + "zone.ZONE_NAME, "
                + "master_zone.MASTER_ZONE_CODE,"
                + "city.CITY_NAME, "
                + "state.STATE_NAME, "
                + "hospital.HOSPITAL_ADDRESS,"
                + "hospital.DAY_CARE,"
                + "hospital.SPECIALITY,"
                + "hospital.DEPOSIT_LESS,"
                + "hospital.IPD,hospital.OPD,hospital.DIAGNOSTICS "
                + "FROM hospital LEFT JOIN zone on hospital.ZONE_ID= zone.ZONE_ID LEFT JOIN master_zone on zone.MASTER_ZONE_ID=master_zone.MASTER_ZONE_ID LEFT JOIN city on master_zone.CITY_ID=city.CITY_ID LEFT JOIN state on city.STATE_ID=state.STATE_ID  WHERE 1 ";

        if (zoneId != 0) {
            sql += " AND hospital.ZONE_ID=" + zoneId;
        }
        if (masterZoneId != 0) {
            sql += " AND zone.MASTER_ZONE_ID=" + masterZoneId;
        }
        if (cityId != 0) {
            sql += " AND master_zone.CITY_ID=" + cityId;
        }
        
        if (tieUpId == 1) {
            sql += " AND hospital.TIE_UP_STAGE=3 AND hospital.IS_INSURANCE=TRUE " ;
        }
        if (tieUpId == 2) {
            sql += " AND hospital.TIE_UP_STAGE=3 AND hospital.IS_INSURANCE=FALSE";
        }
        if (tieUpId == 3) {
            sql += " AND hospital.IS_INSURANCE=TRUE AND hospital.TIE_UP_STAGE!=3";
        }
        if (tieUpId == 4) {
            sql += " AND hospital.TIE_UP_STAGE!=3 AND hospital.IS_INSURANCE=FALSE";
        }
        jdbcTemplate = new JdbcTemplate(this.dataSource);
        List<Map<String, Object>> map;
        try {
            map = jdbcTemplate.queryForList(sql);
            return map;
        } catch (Exception e) {
            return null;
        }
    }

    @Override
    @Transactional
    public int addZip(ZipCodeArea zipCodeArea) {
        try {
            return (Integer) this.hibernateTemplate.save(zipCodeArea);
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    @Transactional
    public List<ZipCodeArea> getzipCodeList() {
        return this.hibernateTemplate.find("from ZipCodeArea");
    }

    @Override
    @Transactional
    public ZipCodeArea getZipCodeById(int zipCodeId) {
        session = this.sessionFactory.getCurrentSession();
        query = session.createQuery("from ZipCodeArea where zipCodeId=" + zipCodeId);
        return (ZipCodeArea) query.uniqueResult();
    }

    @Override
    @Transactional
    public int updateZip(ZipCodeArea zipCodeArea, int zipCodeId) {
        session = this.sessionFactory.getCurrentSession();
        String hql = "Update "
                + " ZipCodeArea "
                + " set "
                + " area=:area,"
                + " zipCode=:zipCode,"
                + "masterZone.masterZoneId=:masterZoneId"
                + " WHERE zipCodeId=:zipCodeId";
        query = session.createQuery(hql);
        query.setParameter("zipCode", zipCodeArea.getZipCode());
        query.setParameter("area", zipCodeArea.getArea());
        query.setParameter("zipCodeId", zipCodeId);
        query.setParameter("masterZoneId", zipCodeArea.getMasterZone().getMasterZoneId());
        int rowCount = query.executeUpdate();

        return rowCount;
    }

    @Override
    @Transactional
    public ZipCodeArea getZipCodeByZipCode(String zipCode) {
        String hqlQuery = "from ZipCodeArea z  WHERE z.zipCode=:zipCode";

        try {
            session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
            query = session.createQuery(hqlQuery).setParameter("zipCode", zipCode);
            return (ZipCodeArea) query.uniqueResult();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    @Transactional
    public List<HealthSpringCenter> getCenterList() {
        session = this.hibernateTemplate.getSessionFactory().getCurrentSession();
        Criteria criteria = session.createCriteria(HealthSpringCenter.class, "center");
        criteria.createAlias("masterZone", "masterZone", CriteriaSpecification.LEFT_JOIN);
        criteria.createAlias("masterZone.cityId", "city", CriteriaSpecification.LEFT_JOIN);
        criteria.createAlias("city.stateId", "state", CriteriaSpecification.LEFT_JOIN);
        ProjectionList projectionList = Projections.projectionList();
        projectionList.add(Projections.property("center.centerId"), "centerId");
        projectionList.add(Projections.property("center.centerName"), "centerName");
        projectionList.add(Projections.property("center.address"), "address");
        projectionList.add(Projections.property("center.phoneNumber"), "phoneNumber");
        projectionList.add(Projections.property("center.latitude"), "latitude");
        projectionList.add(Projections.property("center.longitude"), "longitude");
        criteria.setProjection(projectionList);
        criteria.add(Restrictions.ne("center.centerId", CENTER_ID_YET_TO_CHOOSE));
        criteria.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        criteria.setFetchMode("center", FetchMode.SELECT);

        List list = criteria.list();
        return list;

    }

    @Override
    @Transactional
    public List<Hospital> hospitalListForAutoComplete(String hospitalName) {
        session=this.hibernateTemplate.getSessionFactory().getCurrentSession();
        //return this.hibernateTemplate.find("From Hospital h Where h.hospitalName like '%" +hospitalName+ "%'");
        Criteria cr = session.createCriteria(Hospital.class);
        cr.add(Restrictions.like("hospitalName",hospitalName, MatchMode.ANYWHERE));
        //query = session.createQuery("From Hospital h Where h.hospitalName like '%" +hospitalName+ "%'");
        //return query.list();
        
        return cr.list();
    }
    
    @Override
    @Transactional
    public List<Hospital> smsHospitalListForAutoComplete(String hospitalName) {
        session=this.hibernateTemplate.getSessionFactory().getCurrentSession();
        Criteria cr = session.createCriteria(Hospital.class);
        cr.add(Restrictions.like("hospitalName", hospitalName, MatchMode.ANYWHERE));
        //cr.add(Restrictions.eq("tieUpStage",TIE_UP_STAGE));
        //cr.add(Restrictions.isNotNull("contactSms"));
        return cr.list();
    }
}
